package com.bankLogisticsSys.service;

import java.sql.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.bankLogisticsSys.util.ExcelExportUtil;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ManageReportService {
	
	//较复杂的SQL实现，不使用
	/*
	public List<Record> getReportList(int bank){
		String sqlEMS = "select id,network as bank, "
				+ "(select pathID from expresspath where networkid = networkinfo.id and sequence =2)"
				+ "ems, "
				+ "(select count(*) from packageinfo where type = 1 and id in("
				+ "(select packageID from expresscontent where trackingID in "
				+ "(select trackingID from expressinfo where source = networkinfo.id and sendDate = ?))))"
				+ "bigPackage, "
				+ "(select count(*) from packageinfo where type = 0 and id in("
				+ "(select packageID from expresscontent where trackingID in "
				+ "(select trackingID from expressinfo where source = networkinfo.id and sendDate = ?))))"
				+ "smallPackage, "
				+ "(select count(*) from expresscontent where trackingID in "
				+ "(select trackingID from expressinfo where destination = networkinfo.id and sendDate = ?))"
				+ "emptyPackage, "
				+ "("
				+ "(select count(*) from expressrecord where trackingID in "
				+ "(select trackingID from expressinfo where source = networkinfo.id and sendDate = ?) and networkid=2)"
				+ "-"
				+ "(select count(*) from expresscontent where trackingID in "
				+ "(select trackingID from expressinfo where source = networkinfo.id and sendDate = ?))"
				+ ")"
				+ "abnormalPackage "
				+ "from networkinfo "
				+ "where type = 1";
		String condition = "";
		if(bank !=1){
			condition = " and bank = " + bank;
		}
		
		java.util.Date date = new java.util.Date(System.currentTimeMillis());
		Date currentDate = new Date(date.getTime());
		List<Record> reportList = Db.find(sqlEMS+ condition, currentDate, currentDate, currentDate, currentDate,currentDate);
		for(Record x:reportList){
			int networkid = x.getInt("ems");
			String network = Db.queryStr("select network from networkinfo where id = ?", networkid);
			x.set("ems", network);
		}
		return reportList;
	}
	*/
	
	
	public List<Record> getReportList2(int bank, String networkinfo, Date searchDate){
		//获取SQL语句 manageSQL.sql
		String bigPackage = Db.getSql("report.bigPackage");
		String smallPackage = Db.getSql("report.smallPackage");
		String networkSQL = Db.getSql("report.network");
		String emptyPackage = Db.getSql("report.emptyPackage");
		String normalPackage = Db.getSql("report.normalPackage");
		String ems = Db.getSql("report.ems");
		
		String condition = "";
		if(bank !=1){
			condition = " and bank = " + bank;
		}
		if(StringUtils.isNotBlank(networkinfo)){
			condition += " and ni.network like " + "'%"+networkinfo+"%'";
		}
		System.out.println(networkSQL+condition);
		List<Record> reportList = Db.find(networkSQL+ condition);
		for(Record x:reportList){
			int networkid = x.getInt("id");
			int pathid = x.getInt("pathid");
			Date currentDate = searchDate;
			long bigPk = Db.queryLong(bigPackage,networkid,currentDate);
			long smallPk = Db.queryLong(smallPackage,networkid,currentDate);
			long emptyPk = Db.queryLong(emptyPackage,networkid,currentDate);
			long abnormalPk = bigPk + smallPk - Db.queryLong(normalPackage,networkid,currentDate);
			x.set("bigPackage", bigPk);
			x.set("smallPackage", smallPk);
			x.set("emptyPackage", emptyPk);
			x.set("abnormalPackage", abnormalPk);
			x.set("ems", Db.query(ems,pathid));
		}
		return reportList;
	}
	
	//获取指定月份每天的报表
	public List<Record> getMonthReportList(int bank,int year, int month){
		int days = ExcelExportUtil.getMonthLastDay(year, month);
		
		//获取SQL语句 manageSQL.sql
		String bigPackage = Db.getSql("report.bigPackage");
		String smallPackage = Db.getSql("report.smallPackage");
		String network = Db.getSql("report.network");
		
		String condition = "";
		if(bank !=1){
			condition = " and bank = " + bank;
		}
		System.out.println(network+condition);
		List<Record> reportList = Db.find(network+ condition);
		Record total = new Record();
		total.set("network", "总计");
		
		for(Record x:reportList){
			int networkid = x.getInt("id");
			for(int i=1;i<=days;i++){
				@SuppressWarnings("deprecation")
				Date date = new Date(year, month, i);
				long bigPk = Db.queryLong(bigPackage,networkid,date);
				long smallPk = Db.queryLong(smallPackage,networkid,date);
				x.set("bigPackage"+i, bigPk);
				x.set("smallPackage"+i, smallPk);
			}
		}
		for(int i=1;i<=days;i++){
			long smallPackageNum = 0;
			long bigPackageNum = 0;
			for(Record y:reportList){
				smallPackageNum += y.getLong("smallPackage"+i);
				bigPackageNum += y.getLong("bigPackage"+i);
			}
			total.set("bigPackage"+i, bigPackageNum);
			total.set("smallPackage"+i, smallPackageNum);
		}
		reportList.add(total);
		return reportList;
	}
	//获取指定月份的综合报表，银行网点，封包，揽收，接收，验收
	public List<Record> getMonthTotalList(int bank,int year, int month){
		int days = ExcelExportUtil.getMonthLastDay(year, month);
		
		String networkSQL = Db.getSql("report.network");
		String condition = "";
		if(bank !=1){
			condition = " and bank = " + bank;
		}
		System.out.println(networkSQL+condition);
		List<Record> reportList = Db.find(networkSQL+ condition);
		
		//获取sql语句 manageSQL.sql
		String pack_num = Db.getSql("report.pack_num");
		String get_num = Db.getSql("report.get_num");
		String receive_num = Db.getSql("report.receive_num");
		String accept_num = Db.getSql("report.accept_num");
		for(Record x:reportList){
			int networkid = x.getInt("id");

			long packNum = 0;
			long getNum = 0;
			long receiveNum = 0;
			long acceptNum = 0;
			
			for(int i=1;i<=days;i++){
				@SuppressWarnings("deprecation")
				Date date = new Date(year, month, i);
				packNum += Db.queryLong(pack_num,networkid,date);
				getNum += Db.queryLong(get_num,networkid,date);
				receiveNum += Db.queryLong(receive_num,networkid,date);
				acceptNum += Db.queryLong(accept_num,networkid,date);
			}
			x.set("pack", packNum);
			x.set("get", getNum);
			x.set("receive", receiveNum);
			x.set("accept", acceptNum);
		}
		return reportList;
	}
}
